﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO
Public Class LichTuyenLapDao
    Dim provider As New DataProvider

    Public Function LayDanhSachLichTuyenLap() As DataTable
        Dim sql As String = "SELECT matuyen ,thoidiemkhoihanh FROM LichTuyenLap "
        Return provider.ThucThiCauTruyVan(sql)

    End Function
    Public Function LayDanhSachLichTuyenLapThu() As DataTable

        Dim sql As String = "SELECT  DISTINCT maTuyen,thuhai,thuba,thutu,thunam,thusau,thubay,chunhat FROM LichTuyenLap "
        Return provider.ThucThiCauTruyVan(sql)

    End Function

    'Public Function LayDanhSachLichTuyenLapThu(ByVal maTuyen As Integer) As DataTable

    '    Dim sql As String = "SELECT thuhai,thuba,thutu,thunam,thusau,thubay,chunhat FROM LichTuyenLap where matuyen = " & maTuyen
    '    Return provider.ThucThiCauTruyVan(sql)

    'End Function

    Public Function ThemThoiDiemKhoiHanh(ByVal thoiDiem As LichTuyenDto) As Integer

        Dim sql As String = "INSERT INTO LichTuyenLap(MaTuyen,ThoiDiemKhoiHanh,ThuHai,ThuBa,ThuTu,ThuNam,ThuSau,ThuBay,ChuNhat,MaNhanVien) VALUES(?,?,?,?,?,?,?,?,?,?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", thoiDiem.MaTuyen))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", thoiDiem.ThoiDiemKhoiHanh))
        dsParameter.Add(New OleDbParameter("@ThuHai", thoiDiem.ThuHai))
        dsParameter.Add(New OleDbParameter("@ThuBa", thoiDiem.ThuBa))
        dsParameter.Add(New OleDbParameter("@ThuTu", thoiDiem.ThuTu))
        dsParameter.Add(New OleDbParameter("@ThuNam", thoiDiem.ThuNam))
        dsParameter.Add(New OleDbParameter("@ThuSau", thoiDiem.ThuSau))
        dsParameter.Add(New OleDbParameter("@ThuBay", thoiDiem.ThuBay))
        dsParameter.Add(New OleDbParameter("@ChuNhat", thoiDiem.ChuNhat))
        dsParameter.Add(New OleDbParameter("@MaNhanVien", thoiDiem.MaNhanVien))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)

    End Function

    Public Function CapNhatThoiDiemKhoiHanh(ByRef data As DataTable) As Integer

        Dim dataAdapter As New OleDbDataAdapter()
        Dim con As OleDbConnection = provider.Connect()

        Dim sqlInsert As String = "INSERT INTO LichTuyenLap(MaTuyen,ThoiDiemKhoiHanh) VALUES(@MaTuyen,@ThoiDiemKhoiHanh)"
        Dim commandInsert As New OleDbCommand(sqlInsert, con)
        commandInsert.Parameters.Add(New OleDbParameter("@MaTuyen", OleDbType.Integer, 4, "MaTuyen"))
        commandInsert.Parameters.Add(New OleDbParameter("@ThoiDiemKhoiHanh", OleDbType.VarChar, 10, "ThoiDiemKhoiHanh"))
        dataAdapter.InsertCommand = commandInsert

        Dim sqlDelete As String = "Delete From LichTuyenLap Where MaTuyen=@MaTuyen And ThoiDiemKhoiHanh=@ThoiDiemKhoiHanh"
        Dim commandDelete As New OleDbCommand(sqlDelete, con)
        commandDelete.Parameters.Add(New OleDbParameter("@MaTuyen", OleDbType.Integer, 4, "MaTuyen"))
        commandDelete.Parameters.Add(New OleDbParameter("@ThoiDiemKhoiHanh", OleDbType.VarChar, 10, "ThoiDiemKhoiHanh"))
        dataAdapter.DeleteCommand = commandDelete

        Return dataAdapter.Update(data)

    End Function
    Public Function CapNhatThuLap(ByVal row As DataRow)

        Dim sql As String = "UPDATE LichTuyenLap SET ThuHai=?,ThuBa=?,ThuTu=?,ThuNam=?,ThuSau=?,ThuBay=?,ChuNhat=? Where MaTuyen=?"
        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@ThuHai", row("ThuHai")))
        dsParameter.Add(New OleDbParameter("@ThuBa", row("ThuBa")))
        dsParameter.Add(New OleDbParameter("@ThuTu", row("ThuTu")))
        dsParameter.Add(New OleDbParameter("@ThuNam", row("ThuNam")))
        dsParameter.Add(New OleDbParameter("@ThuSau", row("ThuSau")))
        dsParameter.Add(New OleDbParameter("@ThuBay", row("ThuBay")))
        dsParameter.Add(New OleDbParameter("@ChuNhat", row("ChuNhat")))
        dsParameter.Add(New OleDbParameter("@MaTuyen", row("MaTuyen")))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function
    Public Function XoaThoiDiemKhoiHanh(ByVal row As DataRow)
        Dim sql = "DELETE FROM LichTuyenLap WHERE MaTuyen = ? and ThoiDiemKhoiHanh = ? "

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", row("MaTuyen")))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", row("ThoiDiemKhoiHanh")))
        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

End Class
